-- --------------------------------------
-- Procedure structure for base
-- --------------------------------------
-- 初始化基本结构

-- 创建缓存的基本结构
CREATE SERVER IF NOT EXISTS redis_server 
	FOREIGN DATA WRAPPER redis_fdw
	OPTIONS (address '127.0.0.1', port '6379');

CREATE USER MAPPING FOR PUBLIC
	SERVER redis_server
	OPTIONS (password '180506');


-- 获取所有表名
CREATE OR REPLACE FUNCTION "get_tables"()
RETURNS TABLE(table_name NAME) AS 
$$
    SELECT tablename FROM pg_tables WHERE schemaname='public';
$$ 
LANGUAGE SQL;


-- 获取表的字段信息
CREATE OR REPLACE FUNCTION "get_fields"(IN tbname VARCHAR(40))
RETURNS TABLE(NAME NAME, TYPE TEXT) AS 
$$
    SELECT A.attname AS NAME,
        FORMAT_TYPE(A.atttypid, A.atttypmod) AS TYPE
    FROM PG_CLASS AS C, pg_attribute AS A
    WHERE C.relname = tbname
    AND A.attrelid = C.oid
    AND A.attnum > 0;
$$ 
LANGUAGE SQL;


-- 获取表的字段名称
CREATE OR REPLACE FUNCTION "get_fields_name"(IN tbname VARCHAR(40))
RETURNS SETOF NAME AS 
$$
    SELECT NAME FROM get_fields(tbname);
$$ 
LANGUAGE SQL;


-- 获取表的字段类型
CREATE OR REPLACE FUNCTION "get_field_type"(IN tbname VARCHAR(40), IN field VARCHAR(40))
RETURNS TEXT AS 
$$
    SELECT TYPE FROM get_fields(tbname) WHERE NAME = field;
$$ 
LANGUAGE SQL;


-- 获取表的主键信息
CREATE OR REPLACE FUNCTION "get_primary_key_info"(IN tbname VARCHAR(40))
RETURNS TABLE(pk_name NAME, col_name NAME, type_name NAME) AS 
$$
    SELECT pg_constraint.conname AS pk_name,
        pg_attribute.attname AS col_name,
        pg_type.typname AS type_name 
    FROM pg_constraint  
    INNER join pg_class ON pg_constraint.conrelid = pg_class.oid 
    INNER join pg_attribute ON pg_attribute.attrelid = pg_class.oid  AND  pg_attribute.attnum = pg_constraint.conkey[1]
    INNER join pg_type ON pg_type.oid = pg_attribute.atttypid
    WHERE pg_class.relname = tbname
    AND pg_constraint.contype = 'p'
    AND pg_table_is_visible(pg_class.oid);
$$
LANGUAGE SQL;


-- 获取表的主键自增序列
CREATE OR REPLACE FUNCTION "get_auto_increment"(IN tbname VARCHAR(40))
RETURNS BIGINT AS 
$$
DECLARE 
		pk_name TEXT;
		sql_str TEXT;
		auto_incr BIGINT;
BEGIN
		SELECT get_primary_key(tbname) INTO pk_name;
		SELECT format('%s_%s_seq', tbname, 'id') INTO sql_str;
		EXECUTE 'SELECT last_value FROM ' || sql_str INTO auto_incr;
		RETURN auto_incr;
END;
$$ 
LANGUAGE plpgsql;


-- 获取GUID
CREATE OR REPLACE FUNCTION "guid"()
RETURNS TEXT AS 
$$
    SELECT regexp_replace(''||uuid_generate_v4(),'-','','g');
$$ 
LANGUAGE SQL;

